Re: [SQL] Order by

At 13:45 +0300 on 02/06/1999, Jan Wieck wrote:


>     That's all right and I'm not going to allow this just for the
>     ORDER BY.  It will be enabled  as  kind  of  a  side  effect.
>     Because   view's   can   have   a   GROUP   BY   clause,  the
>     rewriter/planner  must  be  able  to  produce   plans   where
>     different  left-/righttrees have their own grouping. Grouping
>     requires  a  sort  and  thus  sorting  of  subplans  must  be
>     supported.
>
>     Why should we deny ORDER BY on views explicitly?

The SQL92 standard, if I read it right, defines the view by a "query
expression", which in its turn relies on "query specification", which in
turn relies on "table expression", which does not include ORDER BY.

In short, ORDER BY is not part of the query syntax used in the definition
of views, because order is meaningless in views.

The basic idea is that we should get the same results if we use standard
SQL whether we run on PostgreSQL or on Oracle. So, one may write an
application which selects the data from the view, and relies on the order
in the view for something (like grouping). The same program won't work in
any other database system, because you simply can't make that assumption
without adding 'ORDER BY' to your actual query.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma